//done
package Server;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Objects;
import java.util.Vector;

import Common.Settings;

/**
 * @author ameer
 * @author waleed
 * @author bshara
 * @author kamil
 */

public class SQL_Manager  extends ConnectionTOmysql{
	
	private Connection connection;

	/**
	 * This is the default constructor
	 */
	public SQL_Manager(){
		super();
		this.connection = super.getConnection();
	}
	
	/**
	 * This fuction return the employee role 
	 * @param username
	 * @param password
	 * @return
	 */
	public String passwordAndUserNameCheck(String username, String password)
	{
		Statement stmt;
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT role FROM employee WHERE ePassword = '"+password+"' AND eUsername = '"+username+"';");
			String str = "";
			if (rs.next())
			{
				 str = rs.getString(1);
				 rs.close();
			} 
			else
				str = "Ilegalusername";			
			return str;
		} catch (SQLException e)
		{
			return null;
		}		
	}
	
	/**
	 * This fuction inserts a product to data base
	 * @param price
	 * @param name
	 * @param description
	 * @param prodID
	 * @param prodPic
	 * @return true on success else false
	 */
	public boolean insertProduct(double price, String name, String description, long prodID, String ProdPic){		
		try{
			PreparedStatement ps = connection.prepareStatement("INSERT INTO product VALUES (?,?,?,?,LOAD_FILE('"+ProdPic+"'))");
			ps.setDouble(1, price);
			ps.setString(2, name);
			ps.setString(3, description);
			ps.setLong(4, prodID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e){
			return false;
		}
	}
	
	/**
	 * This fuction inserts an updated product to data base
	 * @param price
	 * @param name
	 * @param description
	 * @param prodID
	 * @param prodPic
	 * @return
	 */
	public boolean updateProduct(double price, String name,String description, long prodID, String ProdPic){		
		try {
			PreparedStatement ps = connection.prepareStatement("UPDATE product SET price=?, name=?, description=? , prodPic=LOAD_FILE('"+ProdPic+"') WHERE prodID=?;");
			ps.setDouble(1, price);
			ps.setString(2, name);
			ps.setString(3, description);
			ps.setLong(4, prodID);
			ps.executeUpdate();
			ps.close();
			return true;
		} catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * this fuction is to search for all products
	 * @param productID
	 * @return a Set of all products
	 * @throws IOException 
	 */
	public String[] searchForProduct(long productID) throws IOException
	{
		Statement stmt;
		String[] productTostr=null;
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM product WHERE prodID = "+productID+";");
			productTostr = new String[4];
			java.sql.Blob blob = null;
			if (rs.next())
			{	
				productTostr[0] = rs.getString(1);
				productTostr[1] = rs.getString(2);
				productTostr[2] = rs.getString(3);
				productTostr[3] = rs.getString(4);	
				blob = rs.getBlob("prodPic");
				 
				writeBlobFile(rs.getString("prodPic"), blob.getBytes(1,(int) blob.length())); 
				rs.close();				
			}
			return productTostr;
		} catch (SQLException e)
		{
			return null;
		}
	}
	
	private void writeBlobFile(String filename, byte[] data) {
		try {			
			FileOutputStream fos = new FileOutputStream("C:/"+filename+".jpg");
			fos.write(data);
			fos.close();
			} catch (Exception e) {
			e.printStackTrace();
			} 
	}

	/**
	 * this fuction deletes a product
	 * @param ProductID
	 * @return OK if success, ERROR if failed
	 */
	public String RemoveProduct(Long ProductID)
	{
		try 
		{
			PreparedStatement ps = connection.prepareStatement("DELETE FROM product WHERE prodID=?;");
			ps.setDouble(1, ProductID);
			ps.executeUpdate();
			ps.close();
			return "OK";
		} catch (SQLException e)
		{
		 	return "ERROR";
		}
	}
	
	/**
	 * this fuction loads all products
	 * @return a Set of (array of Long) of products ID
	 */
	public long[] loadAllProductID()
	{
		Statement stmt;
		long[] productsID = null;
		int len=1, i=0;
		Vector<Integer> VecproductID=new Vector<Integer>();
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT prodID FROM product;");
			while(rs.next())
			{
				VecproductID.add(rs.getInt(1));
			}	
			len = VecproductID.size();
			productsID = new long[len];
			for (i=0; i < len; i++)
				productsID[i] = VecproductID.get(i);
			
			rs.close();
			return productsID;
		} catch (SQLException e)
		{
			return null;
		}	
	}
	
	/**
	 * this fuction returns all products
	 * @return array of String of all Products 
	 */
	public String[] loadAllProductsNames()
	{
		Statement stmt;
		String[] productName = null;
		int len=1, i=0;
		Vector<String> sectionVec=new Vector<String>();
		String sqlmsg = String.format("SELECT * FROM product;");
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			while(rs.next())
			{
				sectionVec.add(rs.getString(4)+": "+rs.getString(2));
			}	
			len = sectionVec.size();
			productName = new String[len];
			for (i=0; i < len; i++)
				productName[i] = sectionVec.get(i);
			
			rs.close();
			return productName;
		} catch (SQLException e)
		{
			return null;
		}	
	}
	/**
	 * this fuction returns all products in category
	 * @param categoryID
	 * @return array of String of all category products
	 */
	public String[] loadCategoryProducts(int categoryID)
	{
		Statement stmt;
		String[] products = null;
		int len=1, i=0;
		Vector<String> Vecproduct=new Vector<String>();
		String sqlmsg = String.format("SELECT C.prodID, C.description ,C.name " +
				",C.price FROM product AS C, typescategory AS S, productstype AS P WHERE " +
				"S.categoryID1 = %d AND S.typeID1 = P.typeID AND " +
				"P.prodID = C.prodID;", categoryID);
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			while(rs.next())
			{
				Vecproduct.add(String.format("PID: %-15s Description: %-30s  " +
						"Name: %-20s Price: %-10s", rs.getString(1),rs.getString(2),
						rs.getString(3),rs.getString(4)));
			}	
			len = Vecproduct.size();
			products = new String[len];
			for (i=0; i < len; i++)
				products[i] = Vecproduct.get(i);
			
			rs.close();
			return products;
		} catch (SQLException e)
		{
			return null;
		}	
	}
	
	/**
	 * This fuction returns all categories of a product	
	 * @param prodID
	 * @return a Set of all categories of 
	 */
	public int[] loadAllCategoriesOfProduct(long prodID)
	{
		Statement stmt;
		int[] categoryList = null;
		try
		{
			stmt = connection.createStatement();
			String sqlmsg = String.format("SELECT DISTINCT categoryID1 FROM typescategory WHERE typeID1 IN ( SELECT typeID AS typeID1 FROM productstype WHERE prodID = %d);", prodID);
			ResultSet rs = stmt.executeQuery(sqlmsg);
			Vector<Integer> vecSection = new Vector<Integer>();
			while(rs.next())
			{
				vecSection.add(rs.getInt(1));
			}	
			int i ,len = vecSection.size();
			categoryList = new int[len];
			for (i=0; i < len; i++)
				categoryList[i] = vecSection.get(i);			
			rs.close();
			return categoryList;
		} catch (SQLException e)
		{
			return null;
		}
		catch (Exception exc)
		{
			return null;
		}		
	}
	
	/**
	 * this fuction returns all categories	
	 * @return a Set of all categories
	 */
	public String[] loadAllCategories()
	{
		Statement stmt;
		String[] sectionlist = null;
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM category;");
			Vector<String> vecSection = new Vector<String>();
			while(rs.next())
			{
				vecSection.add((String.valueOf(rs.getInt(1)))+":"+rs.getString(2));
			}	
			int i ,len = vecSection.size();
			sectionlist = new String[len];
			for (i=0; i < len; i++)
				sectionlist[i] = vecSection.get(i);			
			rs.close();
			return sectionlist;
		} catch (SQLException e)
		{
			return null;
		}
		catch (Exception exc)
		{
			return null;
		}		
	}
	
	/**
	 * This fuction adds a new category to data base
	 * @param categoryID
	 * @param CategoryName
	 * @return true on success , false on error.
	 */
	public boolean addCategory(int categoryID, String CategoryName)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("INSERT INTO category VALUES (?,?);");
			ps.setInt(1, categoryID);
			ps.setString(2, CategoryName);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This fuction updates category details in data base
	 * @param categoryID
	 * @param CategoryName
	 * @return true on success , false on error.
	 */
	public boolean updateCategory(int categoryID, String CategoryName)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("UPDATE category SET categoryName=? WHERE categoryID=?;");
			ps.setString(1, CategoryName);
			ps.setInt(2, categoryID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This fuction remove category details from data base
	 * @param categoryID
	 * @return true on success , false on error.
	 */
	public boolean removeCategory(int categoryID)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("DELETE FROM category WHERE categoryID=?;");
			ps.setInt(1, categoryID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This fuction remove Type details from data base.
	 * @param typeID
	 * @return true on success , false on error.
	 */
	public boolean removeType(int typeID)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("DELETE FROM type WHERE typeID=?;");
			ps.setInt(1, typeID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This fuction update type details in data base.
	 * @param typeID
	 * @param typeName
	 * @return true on success , false on error.
	 */
	public boolean updateType(int typeID, String typeName)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("UPDATE type SET typeName=? WHERE typeID=?;");
			ps.setString(1, typeName);
			ps.setInt(2, typeID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This fuction add type details to data base : update table type.
	 * @param typeID
	 * @param typeName
	 * @return true on success , false on error.
	 */
	public boolean addtype(int typeID, String typeName)
	{		
		try
		{
			PreparedStatement ps = connection.prepareStatement("INSERT INTO type VALUES (?,?);");
			ps.setInt(1, typeID);
			ps.setString(2, typeName);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * this fuction returns all types
	 * @return array of String of all types
	 */
	public String[] loadAllTypes()
	{
		Statement stmt;
		String[] type = null;
		int len=1, i=0;
		Vector<String> typeVec=new Vector<String>();
		String sqlmsg = String.format("SELECT * FROM type;");
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			while(rs.next())
			{
				typeVec.add(rs.getString(1)+":"+rs.getString(2));
			}	
			len = typeVec.size();
			type = new String[len];
			for (i=0; i < len; i++)
				type[i] = typeVec.get(i);
			
			rs.close();
			return type;
		} catch (SQLException e)
		{
			return null;
		}	
	}
	
	/**
	 * this fuction returns all products details from a type
	 * @param typeID
	 * @return array of String of all type products
	 */
	public String[] loadTypeProduct(int typeID)
	{
		Statement stmt;
		String[] products = null;
		int len=1, i=0;
		Vector<String> Vecproduct=new Vector<String>();
		String sqlmsg = String.format("SELECT C.prodID, C.description ,C.name " +
				",C.price FROM product AS C, productstype AS P WHERE " +
				"P.typeID = %d AND P.prodID = C.prodID;", typeID);
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			while(rs.next())
			{
				Vecproduct.add(String.format("PID: %-15s Description: %-30s  " +
						"Name: %-20s Price: %-10s", rs.getString(1),rs.getString(2),
						rs.getString(3),rs.getString(4)));
			}	
			len = Vecproduct.size();
			products = new String[len];
			for (i=0; i < len; i++)
				products[i] = Vecproduct.get(i);
			
			rs.close();
			return products;
		} catch (SQLException e)
		{
			return null;
		}	
	}
	
	/**
	 * This fuction removes a product from a type (on table productstype)
	 * @param typeID The type number
	 * @param productID the product id to remove from type
	 * @return true on success , false on error
	 */
	public boolean removeProductFromType(int typeID, long productID)
	{		 
		try
		{
			PreparedStatement ps = connection.prepareStatement("DELETE FROM productstype WHERE prodID=? AND typeID=?;");
			ps.setLong(1, productID);
			ps.setInt(2, typeID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This fuction adds product to a type (on table productstype)
	 * @param typeID The type number
	 * @param productID the product id to add to the type
	 * @return true on success , false on error
	 */
	public boolean addProductToType(int typeID, long productID)
	{		 
		try
		{
			PreparedStatement ps = connection.prepareStatement("INSERT INTO productstype VALUES (?,?);");
			ps.setLong(1, productID);
			ps.setInt(2, typeID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * this fuction returns a Set of all types in category categoryID
	 * @param categoryID
	 * @return array of String of all category types
	 */
	public String[] loadAllCategoryTypes(int categoryID)
	{
		Statement stmt;
		String[] types = null;
		int len=1, i=0;
		Vector<String> typeVec=new Vector<String>();
		String sqlmsg = String.format("SELECT T.typeID, T.typeName FROM " +
				"type AS T, typescategory AS S WHERE " +
				"S.categoryID1 = %d AND S.typeID1 = T.typeID;", categoryID);
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			while(rs.next())
			{
				typeVec.add(String.format("%s:%s",
						rs.getString(1),rs.getString(2)));
			}	
			len = typeVec.size();
			types = new String[len];
			for (i=0; i < len; i++)
				types[i] = typeVec.get(i);			
			rs.close();
			return types;
		} 
		catch (SQLException e)
		{
			return null;
		}	
	}
	
	/**
	 * This fuction removes a type from category (on table typescategory)
	 * @param typeID The type number
	 * @param productID the product id to remove from type
	 * @return true on success , false on error
	 */
	public boolean removeTypeFromCategory(int categoryID, int typeID)
	{		 
		try
		{
			PreparedStatement ps = connection.prepareStatement("DELETE FROM typescategory " +
					"WHERE categoryID1=? AND typeID1=?;");
			ps.setLong(1, categoryID);
			ps.setInt(2, typeID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This fuction adds a type to category (on table typescategory)
	 * @param categoryID The section number 
	 * @param typeID The type ID to add to section
	 * @return true on success , false on error
	 */
	public boolean addTypeToCategory(int categoryID, int typeID)
	{		 
		try
		{
			PreparedStatement ps = connection.prepareStatement("INSERT INTO typescategory VALUES (?,?);");
			ps.setLong(1, categoryID);
			ps.setInt(2, typeID);
			ps.executeUpdate();
			ps.close();
			return true;
		} 
		catch (SQLException e)
		{
		 	return false;
		}
	}
	
	/**
	 * This fuction return all Customers cID as : long[]
	 * @return all Customers cID as : long[] 
	 */
	public long[] loadAllCustomers()
	{
		Statement stmt;
		long[] customersID = null;
		int len=1, i=0;
		Vector<Long> veCustomersID = new Vector<Long>();
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT cID FROM customer;");
			while(rs.next())
			{
				veCustomersID.add(rs.getLong(1));
			}	
			len = veCustomersID.size();
			customersID = new long[len];
			for (i=0; i < len; i++)
				customersID[i] = veCustomersID.get(i);			
			rs.close();
			return customersID;
		} catch (SQLException e)
		{
			return null;
		}
		catch (Exception exc)
		{
			return null;
		}		
	}
	
	/**
	 * This fuction load from data base customer who has ID : cID.
	 * @param cID the id of the customer to load
	 * @return String[] 
	 */
	public String[] loadCustomer(long cID)
	{
		Statement stmt;
		String[] productTostr=null;
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM customer WHERE cID = "+cID+";");
			productTostr = new String[6];
			if (rs.next())
			{	
				productTostr[0] = String.valueOf(rs.getLong(1));//cID
				productTostr[1] = rs.getString(2);//cName
				productTostr[2] = String.valueOf(rs.getInt(3));//cAge
				productTostr[3] = rs.getString(4);//ctelephoneNumber	
				productTostr[4] = String.valueOf(rs.getInt(5));//cAreaNumber
				productTostr[5] = String.valueOf(rs.getInt(6));//importance
				rs.close();		
				return productTostr;					
			}
			return null;			
		} catch (SQLException e)
		{
			return null;
		}
	}	
		
	
		/**
		 * This fuction returns a string array of Areas ---> area number : area name	 
		 * @return String[]
		 */
		public String[] loadAllAreas()
		{
			Statement stmt;
			String[] areaslist = null;
			try
			{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM addresszone;");
				Vector<String> vecAreas = new Vector<String>();
				while(rs.next())
				{
					vecAreas.add((String.valueOf(rs.getInt(1)))+":"+rs.getString(2));
				}	
				int i ,len = vecAreas.size();
				areaslist = new String[len];
				for (i=0; i < len; i++)
					areaslist[i] = vecAreas.get(i);			
				rs.close();
				return areaslist;
			} catch (SQLException e)
			{
				return null;
			}
			catch (Exception exc)
			{
				return null;
			}		
		}
		
		/**
		 * This fuction insert customer info into Data Base.
		 * @param cid customer id
		 * @param name customer name
		 * @param age customer age
		 * @param area customer area
		 * @param phone customer phone
		 * @param prio customer priority 
		 * @return boolean Return True on success, False on error
		 */
		public boolean addCustomer(long cid, String name, int age, int area, String phone, int prio)
		{		
			try 
			{
				PreparedStatement ps = (PreparedStatement) connection.prepareStatement("INSERT INTO customer VALUES (?,?,?,?,?,?);");
				ps.setLong(1, cid);
				ps.setString(2, name);
				ps.setInt(3, age);
				ps.setString(4, phone);
				if (area == 0)
					ps.setNull(5,area, null);
				else
					ps.setInt(5, area);
				ps.setInt(6, prio);
				ps.executeUpdate();
				
				ps = (PreparedStatement) connection.prepareStatement("INSERT INTO customersmarketsegment VALUES (?,?);");
				ps.setLong(1, 3);
				ps.setLong(2, cid);
				ps.executeUpdate();
				
				ps.close();				
				return true;
			} catch (SQLException e)
				{
				 	return false;
				}
		}
		
		/**
		 * This fuction updates customer info in Data Base.
		 * @param cid customer id
		 * @param name customer name
		 * @param age customer age
		 * @param area customer area
		 * @param phone customer phone
		 * @return boolean Return True on success, False on error
		 */
		public boolean updateCustomer(long cid, String name, int age, int area, String phone)
		{	
			String statementstr = null;
			Statement stmt;
			if (area > 0)
				statementstr = String.format("UPDATE customer SET cName='%s', cAge=%d, " +
						"ctelephoneNumber='%s', cAreaNumber=%d WHERE cID=%d;", name,age,phone,area,cid);
			else
				statementstr = String.format("UPDATE customer SET cName='%s', cAge=%d, " +
						"ctelephoneNumber='%s' WHERE cID=%d;", name,age,phone,cid);
			try 
			{
				stmt = connection.createStatement();
				stmt.executeUpdate(statementstr);
				stmt.close();
				return true;
			} 
			catch (SQLException e)
			{
				return false;
			}
		}
		
		/**
		 * This fuction insert area (Address Zone) into Data Base.
		 * @param areaID
		 * @param areaName
		 * @return boolean Return True on success False on error.
		 */
		public boolean addAddressZone(int areaID, String areaName)
		{		
			try 
			{
				PreparedStatement ps = (PreparedStatement) connection.prepareStatement
				("INSERT INTO addresszone VALUES (?,?);");
				ps.setInt(1, areaID);
				ps.setString(2, areaName);
				ps.executeUpdate();
				ps.close();
				return true;
			} catch (SQLException e)
				{
				 	return false;
				}
		}
	
		/**
		 * This fuction update area (Address Zone) in Data Base.
		 * @param areaID
		 * @param areaName
		 * @return boolean Return True on success False on error.
		 */
		public boolean updateAddressZone(int areaID, String areaName)
		{	
			String statementstr = null;
			Statement stmt;
			statementstr = String.format("UPDATE addresszone SET zoneName='%s'" +
					" WHERE zoneNumber=%d;", areaName,areaID);
			try 
			{
				stmt = connection.createStatement();
				stmt.executeUpdate(statementstr);
				stmt.close();
				return true;
			} 
			catch (SQLException e)
			{
				return false;
			}
		}
		
		/**
		 * This fuction load all customers in market sectors
		 * Return Vector<Long> of all customer in segment.
		 * @param segment
		 * @return  Vector<Long>
		 */
		public long[] loadAllCustomersAtMarketSegment(int segment)
		{
			Statement stmt;
			int i, len;
			long[] customer;
			Vector<Long> Vecsector = new Vector<Long>();
			String sqlmsg = String.format("SELECT cID FROM customersmarketsegment WHERE " +
					"sNumber = %d ;", segment);
			try
			{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sqlmsg);
				while(rs.next())
				{
					Vecsector.add(rs.getLong(1));					
				}	
				len = Vecsector.size();
				customer = new long[len];
				for (i=0; i < len; i++)
					customer[i] = Vecsector.get(i);
				rs.close();
				return customer;
			} catch (SQLException e)
			{
				return null;
			}	
		}
		
		/**
		 * This fuction analyzes the data and update customer importance.
		 * @return string "OK" if success, else "ERROR"
		 */
		public String activityTracking()
		{
			Statement stmt, stmt2;
			PreparedStatement ps = null, ps2 = null;
			String str = "", str2 = "";
			int[] purchasesSum;
			long[] customer;
			int[] rejectedSum;
			long[] customer2;
			int len, len2, i;
			boolean flag=false;
			long importance = 1;
			Vector<String> Vec = new Vector<String>();
			Vector<String> Vec2 = new Vector<String>();
			Vector<String> Vec3 = new Vector<String>();
			String sqlmsg = ("SELECT cID3, COUNT(*) FROM purchases GROUP BY cID3;");
			String sqlmsg2 = ("SELECT cID2, COUNT(*) FROM rejected GROUP BY cID2;");
			try
			{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sqlmsg);
				stmt2 = connection.createStatement();
				ResultSet rs2 = stmt2.executeQuery(sqlmsg2);
				while(rs.next())
				{
					Vec.add(rs.getString(1)+":"+rs.getString(2)+".");	
				}	
				while(rs2.next())
				{
					Vec2.add(rs2.getString(1)+":"+rs2.getString(2)+".");	
				}	
				len = Vec.size();
				len2 = Vec2.size();
				purchasesSum = new int[len];
				customer = new long[len];
				rejectedSum = new int[len2];
				customer2 = new long[len2];
				for (i=0; i < len; i++)
				{
					str = Vec.get(i);
					customer[i] = Long.parseLong(str.substring(0,str.indexOf(":")));
					purchasesSum[i] = Integer.parseInt(str.substring(str.indexOf(":")+1, str.indexOf(".")));
					Vec3 = Vec2;
					for(int j=0 ; j<len2 ; j++) {
						str2 = Vec3.get(j);
						customer2[j] = Long.parseLong(str2.substring(0,str2.indexOf(":")));
						rejectedSum[j] = Integer.parseInt(str2.substring(str2.indexOf(":")+1, str2.indexOf(".")));
						if(customer[i] == customer2[j]){
							importance = purchasesSum[i] / rejectedSum[j];
							if(importance <= 0)
								importance = 1;
							else if(importance > 11)
								importance = 11;
							ps = (PreparedStatement) connection.prepareStatement("UPDATE customer SET importance ="+Objects.toString(importance)+" WHERE cID ="+Objects.toString(customer[i])+";");
							ps.executeUpdate();
							flag = true;
							break;
						}
						if(flag == false) {
							importance = purchasesSum[i];
							if(importance <= 0)
								importance = 1;
							else if(importance > 11)
								importance = 11;
							ps2 = (PreparedStatement) connection.prepareStatement("UPDATE customer SET importance ="+Objects.toString(importance)+" WHERE cID ="+Objects.toString(customer[i])+";");
							ps2.executeUpdate();
						}
						flag = false;
					}					 
				}				
				rs.close();		
				rs2.close();
				return "OK";
			} catch (SQLException e)
			{
				return "ERROR";
			}	
		}
	
		/**
		 * This function loads all interests of customer CustID from DB
		 * Return String[] of all customer interests
		 * @return  String[] on success, null on fail
		 */
		public String[] loadAllCustomerInterests(long CustID){
			Statement stmt;
			String[] CustomerInterests = null;
			int len=1, i=0;
			Vector<String> VecCustomerInterests=new Vector<String>();
			String sqlmsg = String.format("SELECT * FROM customerinterest " +
										"WHERE CustID = %d" , CustID);
			try{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sqlmsg);
				while(rs.next()){
					VecCustomerInterests.add(String.format(rs.getLong(2)+":"+rs.getInt(3)+"."));					
				}	
				len = VecCustomerInterests.size();
				CustomerInterests = new String[len];
				for (i=0; i < len; i++)
					CustomerInterests[i] = VecCustomerInterests.get(i);
				
				rs.close();
				return CustomerInterests;
			} 
			catch (SQLException e){
				return null;
			}	
		}
		
		/**
		 * This function adds a new customer interest to DB
		 * @param CustID The interested customer ID
		 * @param CatID The interested category ID
		 * @param Rate The interest rate
		 * @return "OK" on success, null on fail 
		 */
		public String addCustomerInterest(long CustID, int CatID, int Rate){		
			PreparedStatement ps = null;
			try{
				ps = (PreparedStatement) connection.prepareStatement
						("INSERT INTO customerinterest VALUES (?,?,?);");
				ps.setLong(1, CustID);
				ps.setInt(2, CatID);
				ps.setInt(3, Rate);
				ps.executeUpdate();
				ps.close();
				return "OK";
			} 
			catch (SQLException e){
				 return null;
			}
		}
		
		/**
		 * This function deletes a customer interest from DB
		 * @param CustID The interested customer ID
		 * @param CatID The interested category ID
		 * @return "OK" on success, null on fail 
		 */
		public String deleteCustomerInterest(long CustID, int CatID){		
			PreparedStatement ps = null;
			try{
				ps = (PreparedStatement) connection.prepareStatement
						("DELETE FROM customerinterest WHERE CustID=? AND CategoryID=?;");
				ps.setLong(1, CustID);
				ps.setInt(2, CatID);
				ps.executeUpdate();
				ps.close();
				return "OK";
			} 
			catch (SQLException e){
				 return null;
			}
		}

		/**
		 * This fuction returns a string of Response Report (Marketing manager can produce this report)
		 * @param dealsaleID the Deal Sale ID to do a report
		 * @return String on success, null on fail 
		 */		
		public String getResponseReport(long dealsaleID)
		{
			Statement stmt;
			String str = "";
			String sqlmsg = ("SELECT cID3, COUNT(*) FROM purchases p WHERE DealsaleID3 ="+dealsaleID+" GROUP BY cID3;");
			try
			{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sqlmsg);
				if (rs.next())
				{
					str = str + "Campaign: "+ rs.getLong(1)+" Number of purchases "+rs.getLong(2)+"\n";				
				}
				sqlmsg = ("SELECT response FROM rejected WHERE DealsaleID2 ="+dealsaleID+";");
				rs = stmt.executeQuery(sqlmsg);
				str = str+"All response:\n";
				while(rs.next())
				{
					str = str+rs.getString(1)+"\n";				
				}			
				rs.close();
				return str;
			} catch (SQLException e)
			{
				return null;
			}	
			
		}
		
		/**
		 * This fuction returns a string of customer report.
		 * @return String on success, null on fail 
		 */
		public String customerReport()
		{
			Statement stmt;
			String str = "";
			String sqlmsg = ("SELECT * FROM customer ORDER BY importance;");
			try
			{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sqlmsg);
				while(rs.next())
				{
					str = str+ String.format("%-10s %-10s %-10s %-10s %-10s %-10s \n",
							rs.getString(1), rs.getString(2), rs.getString(3),
					rs.getString(4),rs.getString(5),rs.getString(6));				
				}			
				rs.close();
				return str;
			} catch (SQLException e)
			{
				return null;
			}	
		}

		/**
		 * This function imports all sales men from DB
		 * @return  long[] sales men on success, null on fail
		 */
		public long[] loadAllSalesMen(){
			Statement stmt;
			Vector<Long> salesMenVect = new Vector<Long>();
			long[] salesMen;
			int i, len;
			String sqlmsg = ("SELECT eID FROM employee WHERE role = 'SellsMen';");
			try{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sqlmsg);
				
				while(rs.next()){
					salesMenVect.add(rs.getLong(1));					
				}				
				len = salesMenVect.size();
				salesMen = new long[len];
				for (i=0; i < len; i++){
					salesMen[i] = salesMenVect.get(i);				
				}	
				rs.close();
				return salesMen;
			} 
			catch (SQLException e){
				return null;
			}	
		}
		
		/**
		 * This function updates all sales men schedule
		 * @param schedule
		 * @param dealsaleID
		 * @return  boolean true on success, false on fail
		 */
		public boolean updateSalesManSchedule(String schedule, long dealsaleID){
			PreparedStatement ps = null;
			long salesMen, customer;
			try {
				while (schedule.length() > 0){
					salesMen = Long.parseLong(schedule.substring(0, schedule.indexOf(":")));
					customer = Long.parseLong(schedule.substring(schedule.indexOf(":")+1, schedule.indexOf(".")));
					schedule = schedule.substring(schedule.indexOf("\n")+1, schedule.length());							
					ps = (PreparedStatement) connection.prepareStatement("INSERT INTO salesmanschedule VALUES (?,?,?);");
					ps.setLong(1, salesMen);
					ps.setLong(2, customer);
					ps.setLong(3, dealsaleID);				
					ps.executeUpdate();			
				}
				ps.close();	
				return true;
			} 
			catch (SQLException e){
					return false;
				}
		}
		
		/**
		 * This fuction load all clients for sale men.
		 * @param username the sale man eUsername
		 * @return long[] of all clients for sale men.
		 */
		public long[] loadAllCustomersForSalesMan(String username){
			Statement stmt;
			Vector<Long> VecSalesMan = new Vector<Long>();
			long[] SalesManList;
			int i, len;
			String sqlmsg = ("SELECT DISTINCT cID1 FROM salesmanschedule AS S, employee AS E WHERE " +
					"E.eUsername = '"+username+"' AND E.eID = S.eID"+";");
			try{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sqlmsg);
				
				while(rs.next()){
					VecSalesMan.add(rs.getLong(1));					
				}				
				len = VecSalesMan.size();
				SalesManList = new long[len];
				for (i=0; i < len; i++){
					SalesManList[i] = VecSalesMan.get(i);				
				}	
				rs.close();
				return SalesManList;
			} 
			catch (SQLException e){
				return null;
			}	
			
		}
		
		/**
		 * This fuction load the deal sale for sale man for customer cID
		 * @param cID 
		 * @return  long[] on success, 0 on fail
		 */
		public long loadDealSaleForCustomer(long cID){
			Statement stmt;
			String sqlmsg = ("SELECT SDealsaleID FROM salesmanschedule WHERE " + "cID1 ="+cID+";");
			try{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sqlmsg);
				if (rs.next()){
					return (rs.getLong(1));					
				}				
				rs.close();
				return 0;
			} 
			catch (SQLException e){
				return 0;
			}	
		}
		
		/**
		 * This fuction insert rejected for Deal Sale and deletes the current customer from the current sales man schedule
		 * @param cID
		 * @param dealSaleID
		 * @param message
		 * @param date
		 * @return  boolean true on success, false on fail
		 */
		public boolean rejectedDealSale(long cID, long dealSaleID , String message, Date date){
			PreparedStatement ps;
			try {
				ps = connection.prepareStatement("INSERT INTO rejected VALUES (?,?,?,?);");
				ps.setLong(1, cID);
				ps.setLong(2, dealSaleID);
				ps.setString(3, message);
				ps.setDate(4, date);
				ps.executeUpdate();
				ps = connection.prepareStatement("DELETE FROM salesmanschedule WHERE cID1 = ?;");
				ps.setLong(1, cID);
				ps.executeUpdate();
				ps.close();
				return true;
			}	
			catch(SQLException e){
				return false;
			}
		}
		
		/**
		 * This fuction insert  purchases for Deal Sale
		 * @param cID
		 * @param dealSaleID
		 * @param message
		 * @param date
		 * @return  boolean true on success, false on fail
		 */
		public boolean purchasedDealSale(long cID,long dealSaleID, Date date){
			PreparedStatement ps;
			try {
				ps = connection.prepareStatement("INSERT INTO purchases VALUES (?,?,?);");
				ps.setLong(1, cID);
				ps.setLong(2, dealSaleID);
				ps.setDate(3, date);
				ps.executeUpdate();
				ps = connection.prepareStatement("DELETE FROM salesmanschedule WHERE cID1 = ?;");
				ps.setLong(1, cID);
				ps.executeUpdate();
				ps.close();
				return true;
			}	
			catch(SQLException e){
				return false;
			}
		}
		
		/**
		 * This function return all clients market segments as String[] 
		 * @return String[] 
		 */
		public String[] loadAllMarketSegments(){
			Statement stmt;
			String[] marketsectorsID = null;
			int len=1, i=0;
			Vector<Integer> vecMarketsectorsID = new Vector<Integer>();
			try{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT sNumber FROM marketsegment;");
				while(rs.next()){
					vecMarketsectorsID.add(rs.getInt(1));
				}	
				len = vecMarketsectorsID.size();
				marketsectorsID = new String[len];
				for (i=0; i < len; i++)
					marketsectorsID[i] = String.valueOf(vecMarketsectorsID.get(i));			
				rs.close();
				return marketsectorsID;
			} 
			catch (SQLException e){
				return null;
			}
			catch (Exception exc){
				return null;
			}		
		}
		
		/**
		 * This function creates a new template to DB
		 * @param tempID the template id
		 * @param tempName template name
		 * @param segment the clients segment of the template
		 * @param msg the message to customer
		 * @param prodID the product id of the template
		 * @return  "OK" on success , "IlegalID" on fail
		 */
		public String createTemplate(long tempID, String tempName, String segment, String msg, long prodID ){
			try {
				PreparedStatement ps = connection.prepareStatement("INSERT INTO Template VALUES (?,?,?,?,?);");
				ps.setLong(1, tempID);
				ps.setString(2, tempName);
				ps.setString(3, msg);
				ps.setLong(4, Long.parseLong(segment));		
				ps.setLong(5, prodID);		
				ps.executeUpdate();
				ps.close();
				return "OK";
			}
			catch (SQLException e){
			 	return "IlegalID";
			}
			catch (Exception e){
			 	return null;
			}
		}
		
		/**
		 * This function imports all templates from DB
		 * @return a Set of all Template IDs as array of String
		 */
		public String[] loadAllTemplates(){
			Statement stmt;
			String[] template = null;
			int len=1, i=0;
			Vector<String> Template = new Vector<String>();
			try{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM template;");
				while(rs.next()){
					Template.add(rs.getString(1)+": "+rs.getString(2));
				}	
				len = Template.size();
				template = new String[len];
				for (i=0; i < len; i++)
					template[i] = Template.get(i);			
				rs.close();
				return template;
			} 
			catch (SQLException e){
				return null;
			}
			catch (Exception exc){
				return null;
			}		
		}
		
		/**
		 * This function imports template details from DB
		 * @param tempID the template id
		 * @return array of string of template details
		 */
		public String[] loadTemplate(long tempID){
			Statement stmt;
			String[] templates = null;
			long prodID = 0;
			try{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM template WHERE temID = "+tempID+";");
				templates = new String[8];
				if (rs.next()){	
					templates[0] = String.valueOf(rs.getLong(1)); 
					templates[1] = rs.getString(2); 
					templates[2] = rs.getString(3);
					templates[3] = String.valueOf(rs.getInt(4));
					prodID = rs.getLong(5);
					rs.close();	
					ResultSet Result = stmt.executeQuery("SELECT * FROM product WHERE prodID = "+prodID+";");
					if (Result.next()){
						templates[4] = Result.getString(1);
						templates[5] = Result.getString(2);
						templates[6] = Result.getString(3);
						templates[7] = Result.getString(4);
						return templates;
					}									
				}
				return null;			
			} 
			catch (SQLException e){
				return null;
			}
		}
		
		/**
		 * This function update template details in DB
		 * @param tempID the template id
		 * @param tempName the name of the campaign
		 * @param segment the clients segment of the campaign
		 * @param message the message to customer
		 * @param tempName the id of the product of the campaign
		 * @return "OK" on success , "ERROR" on MySQL error
		 */
		public String updateTemplate(long tempID, String tempName, String segment, String message, long prodID){
			String statementstr = null;
			Statement stmt;
			long tmpsector = Long.parseLong(segment);
			statementstr = String.format("UPDATE Template SET temName='%s', " +
					"message='%s',sectorNumber=%d, productNumber=%d WHERE temID=%d;",
					tempName,message,tmpsector,prodID,tempID);
			try {
				stmt = connection.createStatement();
				stmt.executeUpdate(statementstr);
				stmt.close();
				return "OK";
			} 
			catch (SQLException e){
				return "ERROR";
			}	
		}
		
		/**
		 * This function updates deal sale details in DB
		 * @param dealsaleID the deal sale id
		 * @param startdate the deal sale start date
		 * @param endate the deal sale end date
		 * @param status the deal sale status
		 * @return "OK" on success , "ERROR" on fail
		 */
		public String updateDealSale(long dealsaleID, Date startdate, Date endate, String status){
			try{
				PreparedStatement ps = connection.prepareStatement("UPDATE dealsale SET startdate=?, enddate=?, status=? WHERE DealsaleID=?;");
				ps.setDate(1, startdate);
				ps.setDate(2, endate);
				ps.setString(3, status);
				ps.setLong(4, dealsaleID);			
				ps.executeUpdate();
				ps.close();
				return "OK";
			} 
			catch (SQLException e){
				return "ERROR";
			}	
		}
		
		/**
		 * This function adds a new deal sale to DB
		 * @param dealsaleID the deal sale id
		 * @param startdate the deal sale start date
		 * @param endate the deal sale end date
		 * @param status the deal sale status
		 * @param tempID the template id
		 * @return "OK" on success , "ERROR" on fail
		 */
		public String saveDealSale(long dealsaleID, Date startdate, Date endate, String status , long tempID){
			try{
				PreparedStatement ps = connection.prepareStatement("INSERT INTO dealsale VALUES (?,?,?,?,?);");
				ps.setLong(1, dealsaleID);	
				ps.setLong(2, tempID);	
				ps.setDate(3, startdate);
				ps.setDate(4, endate);
				ps.setString(5, status);				
				ps.executeUpdate();
				ps.close();
				return "OK";
			} 
			catch (SQLException e){
				return "ERROR";
			}	
		}
		
		/**
		 * This function imports deal sale details from DB
		 * @param dealsaleID the deal sale id
		 * @return Object[] on success, null on fail
		 */
		public Object[] loadDealSale(long dealsaleID){
			Statement stmt;
			Object[] dealSales = null;
			try{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM dealsale WHERE DealsaleID = "+dealsaleID+";");
				dealSales = new Object[5];
				if (rs.next()){	
					dealSales[0] = rs.getLong(1);
					dealSales[4] = rs.getLong(2);
					dealSales[1] = rs.getDate(3);
					dealSales[2] = rs.getDate(4);
					dealSales[3] = rs.getString(5);
					rs.close();
					return dealSales;
				}
				return null;			
			} 
			catch (SQLException e){
				return null;
			}		
		}
		
		/**
		 * This function imports all deal sales from DB
		 * @return Vector<Long> on success, null on fail
		 */
		public long[] loadAllDealSales(){
			Statement stmt;
			long[] tempIDs = null;
			int len=1, i=0;
			Vector<Long> dealsaleIDs = new Vector<Long>();
			try{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT DealsaleID FROM dealsale;");
				while(rs.next()){
					dealsaleIDs.add(rs.getLong(1));
				}	
				len = dealsaleIDs.size();
				tempIDs = new long[len];
				for (i=0; i < len; i++)
					tempIDs[i] = dealsaleIDs.get(i);			
				rs.close();
				return tempIDs;
			} 
			catch (SQLException e){
				return null;
			}
			catch (Exception exc){
				return null;
			}		
		}
		
		/**
		 * This fuction loads all interested customers in a category
		 * @param prodID
		 * @return  Vector<Long> of interested customers on success, null on fail
		 */
		public long[] loadAllInterestedCustomers(long prodID)
		{
			Statement stmt;
			int i, len;
			long[] customer;
			Vector<Long> Vecsector = new Vector<Long>();
			String sqlmsg = String.format("SELECT distinct CustID FROM customerinterest WHERE CategoryID IN ( SELECT CategoryID FROM typescategory WHERE typeID1 IN( SELECT typeID AS typeID1 FROM productstype WHERE prodID = %d ));", prodID);
			try
			{
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sqlmsg);
				while(rs.next())
				{
					Vecsector.add(rs.getLong(1));					
				}	
				len = Vecsector.size();
				customer = new long[len];
				for (i=0; i < len; i++)
					customer[i] = Vecsector.get(i);
				rs.close();
				return customer;
			} catch (SQLException e)
			{
				return null;
			}	
		}
}
